﻿using System;
using System.Data;
using System.Data.Common;

namespace Sanhao.DbService
{
    public class DbHelper
    {
        private readonly DbFactory _dbFactory;
        private readonly string _strConn;
        public DbHelper(DbProvider dbProvider, string strConn)
        {
            _dbFactory = new DbFactory(dbProvider);
            _strConn=strConn;
        }

        public DbFactory CurrentDbFactory => this._dbFactory;
        internal string ConnectionString=>this._strConn;

        /// <summary>
        /// Query查询，返回DataTable
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public DataTable GetTable(string strSql)
        {
            return GetTable(strSql, null);
        }

        /// <summary>
        /// Query查询，返回DataTable，支持参数传入
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public DataTable GetTable(string strSql, DbParameter[] pas)
        {
            return GetTable(strSql, pas, CommandType.Text);
        }

        /// <summary>
        /// Query查询，返回DataTable，支持参数和命令类型
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="pas"></param>
        /// <param name="cmdType"></param>
        /// <returns></returns>
        public DataTable GetTable(string strSql, DbParameter[] pas, CommandType cmdType)
        {
            DataTable dt = new DataTable(); ;
            using (DbConnection conn = _dbFactory.CreateDbConnection(_strConn))
            {
                DbDataAdapter da = _dbFactory.CreateDbDataAdapter(strSql, conn);
                da.SelectCommand.CommandType = cmdType;
                if (pas != null)
                {
                    da.SelectCommand.Parameters.AddRange(pas);
                }
                da.Fill(dt);
            }
            return dt;
        }

        /// <summary>
        /// Query查询，返回DataSet
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public DataSet GetDataSet(string strSql)
        {
            return GetDataSet(strSql, null);
        }

        /// <summary>
        /// Query查询，返回DataSet，支持参数传入
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public DataSet GetDataSet(string strSql, DbParameter[] pas)
        {
            return GetDataSet(strSql, pas, CommandType.Text);
        }

        /// <summary>
        /// Query查询，返回DataSet，支持参数和命令类型
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="pas"></param>
        /// <param name="cmdType"></param>
        /// <returns></returns>
        public DataSet GetDataSet(string strSql, DbParameter[] pas, CommandType cmdType)
        {
            DataSet dt = new DataSet();
            using (DbConnection conn = _dbFactory.CreateDbConnection(_strConn))
            {
                DbDataAdapter da = _dbFactory.CreateDbDataAdapter(strSql, conn);
                da.SelectCommand.CommandType = cmdType;
                if (pas != null)
                {
                    da.SelectCommand.Parameters.AddRange(pas);
                }

                da.Fill(dt);
            }

            return dt;
        }

        /// <summary>
        /// 执行存储过程，返回影响行数
        /// </summary>
        /// <param name="procName"></param>
        /// <returns></returns>
        public int ExecuteProc(string procName)
        {
            return ExecuteSql(procName, null, CommandType.StoredProcedure);
        }

        /// <summary>
        /// 执行存储过程，返回影响行数，支持参数传入
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public int ExecuteProc(string procName, DbParameter[] pas)
        {
            return ExecuteSql(procName, pas, CommandType.StoredProcedure);
        }

        /// <summary>
        /// 执行sql命令，返回影响行数
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public int ExecuteSql(string strSql)
        {
            return ExecuteSql(strSql, null);
        }

        /// <summary>
        /// 执行sql命令，返回影响行数，支持参数传入
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public int ExecuteSql(string strSql, DbParameter[] pas)
        {
            return ExecuteSql(strSql, pas, CommandType.Text);
        }

        /// <summary>
        /// 执行sql命令，返回影响行数，支持参数传入和命令类型
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="pas"></param>
        /// <param name="cmdType"></param>
        /// <returns></returns>
        public int ExecuteSql(string strSql, DbParameter[] pas, CommandType cmdType)
        {
            int i = 0;
            using (DbConnection conn = _dbFactory.CreateDbConnection(_strConn))
            {
                DbCommand cmd = _dbFactory.CreateDbCommand(strSql, conn);
                cmd.CommandType = cmdType;
                if (pas != null)
                {
                    cmd.Parameters.AddRange(pas);
                }
                conn.Open();
                i = cmd.ExecuteNonQuery();
                conn.Close();
            }
            return i;
        }

        /// <summary>
        /// 执行sql命令，返回单一值
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public int ExecuteScalarSql(string strSql)
        {
            return ExecuteScalarSql(strSql, null);
        }

        /// <summary>
        /// 执行sql命令，返回单一值，支持参数传入
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public int ExecuteScalarSql(string strSql, DbParameter[] pas)
        {
            return ExecuteScalarSql(strSql, pas, CommandType.Text);
        }

        /// <summary>
        /// 执行sql命令，返回单一值，支持参数传入和命令类型
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="pas"></param>
        /// <param name="cmdType"></param>
        /// <returns></returns>
        public int ExecuteScalarSql(string strSql, DbParameter[] pas, CommandType cmdType)
        {
            int i = 0;
            using (DbConnection conn = _dbFactory.CreateDbConnection(_strConn))
            {
                DbCommand cmd = _dbFactory.CreateDbCommand(strSql, conn);
                cmd.CommandType = cmdType;
                if (pas != null)
                {
                    cmd.Parameters.AddRange(pas);
                }
                conn.Open();
                i = Convert.ToInt32(cmd.ExecuteScalar());
                conn.Close();
            }
            return i;
        }

        /// <summary>
        /// 执行存储过程，返回单一值，支持参数传入
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public int ExecuteScalarProc(string strSql, DbParameter[] pas)
        {
            return ExecuteScalarSql(strSql, pas, CommandType.StoredProcedure);
        }

        /// <summary>
        /// 执行存储过程，返回object
        /// </summary>
        /// <param name="procName"></param>
        /// <returns></returns>
        public object GetObjectByProc(string procName)
        {
            return GetObjectByProc(procName, null);
        }

        /// <summary>
        /// 执行存储过程，返回object，支持参数传入
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public object GetObjectByProc(string procName, DbParameter[] pas)
        {
            return GetObject(procName, pas, CommandType.StoredProcedure);
        }

        /// <summary>
        /// 执行sql，返回object
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public object GetObject(string strSql)
        {
            return GetObject(strSql, null);
        }

        /// <summary>
        /// 执行sql，返回object，支持参数传入
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public object GetObject(string strSql, DbParameter[] pas)
        {
            return GetObject(strSql, pas, CommandType.Text);
        }

        /// <summary>
        /// 执行sql，返回object，支持参数传入和命令类型
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="paras"></param>
        /// <param name="cmdType"></param>
        /// <returns></returns>
        public object GetObject(string strSql, DbParameter[] paras, CommandType cmdType)
        {
            object o = null;
            using (DbConnection conn = _dbFactory.CreateDbConnection(_strConn))
            {
                DbCommand cmd = _dbFactory.CreateDbCommand(strSql, conn);
                cmd.CommandType = cmdType;
                if (paras != null)
                {
                    cmd.Parameters.AddRange(paras);

                }

                conn.Open();
                o = cmd.ExecuteScalar();
                conn.Close();
            }
            return o;
        }

        /// <summary>
        /// 执行存储过程，返回DataReader
        /// </summary>
        /// <param name="procName"></param>
        /// <returns></returns>
        public DbDataReader GetReaderByProc(string procName)
        {
            return GetReaderByProc(procName, null);
        }

        /// <summary>
        /// 执行存储过程，返回DataReader，支持参数传入
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public DbDataReader GetReaderByProc(string procName, DbParameter[] pas)
        {
            return GetReader(procName, pas, CommandType.StoredProcedure);
        }

        /// <summary>
        /// 执行sql，返回DataReader
        /// </summary>
        /// <param name="strSql"></param>
        /// <returns></returns>
        public DbDataReader GetReader(string strSql)
        {
            return GetReader(strSql, null);
        }

        /// <summary>
        /// 执行sql，返回DataReader，支持参数传入
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="pas"></param>
        /// <returns></returns>
        public DbDataReader GetReader(string strSql, DbParameter[] pas)
        {
            return GetReader(strSql, pas, CommandType.Text);
        }

        /// <summary>
        /// 执行sql，返回DataReader，支持参数传入和命令类型
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="pas"></param>
        /// <param name="cmdType"></param>
        /// <returns></returns>
        public DbDataReader GetReader(string strSql, DbParameter[] pas, CommandType cmdType)
        {
            DbDataReader sqldr = null;
            DbConnection conn = _dbFactory.CreateDbConnection(_strConn);
            DbCommand cmd = _dbFactory.CreateDbCommand(strSql, conn);
            cmd.CommandType = cmdType;
            if (pas != null)
            {
                cmd.Parameters.AddRange(pas);
            }
            conn.Open();
            //CommandBehavior.CloseConnection的作用是如果关联的DataReader对象关闭，则连接自动关闭
            sqldr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return sqldr;
        }
        public virtual void BulkInsert(DataTable source, string table)
        {
            throw new NotImplementedException();
        }

    }
}